// $Id: ImportPlugin.cs 66 2008-05-07 19:57:30Z nvivo $

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Reflection;
using System.Text;

namespace DBLGen
{
    /// <summary>
    /// This is the base class for all import plugins.
    /// </summary>
    public abstract class ImportPlugin : Plugin
    {
        /// <summary>
        /// Returns information about all the tables and views from the database. 
        /// </summary>
        /// <returns>An array of <see cref="Table">Tables</see>.</returns>
        public abstract Table[] GetAllTables();

        #region Reference Tables

        /// <summary>
        /// Creates the reference table to store information about tables
        /// used by <see cref="BuildTables" /> method.
        /// </summary>
        public static DataTable CreateTablesReferenceTable()
        {
            DataTable dt = new DataTable("Tables");

            dt.Columns.Add("tableschema", typeof(string));
            dt.Columns.Add("tablename", typeof(string)).AllowDBNull = false;
            dt.Columns.Add("tabletype", typeof(string));

            return dt;
        }

        /// <summary>
        /// Creates the reference table to store information about columns
        /// used by <see cref="BuildTables" /> method.
        /// </summary>
        public static DataTable CreateColumnsReferenceTable()
        {
            DataTable dt = new DataTable("Columns");

            dt.Columns.Add("tableschema", typeof(string));
            dt.Columns.Add("tablename", typeof(string)).AllowDBNull = false;
            dt.Columns.Add("columnorder", typeof(int));
            dt.Columns.Add("columnname", typeof(string)).AllowDBNull = false;
            dt.Columns.Add("type", typeof(string));
            dt.Columns.Add("length", typeof(int));
            dt.Columns.Add("octetlength", typeof(int));
            dt.Columns.Add("precision", typeof(int));
            dt.Columns.Add("scale", typeof(int));
            dt.Columns.Add("isnullable", typeof(bool));
            dt.Columns.Add("iscomputed", typeof(bool));
            dt.Columns.Add("ispk", typeof(bool));
            dt.Columns.Add("isfk", typeof(bool));
            dt.Columns.Add("ref_tableschema", typeof(string));
            dt.Columns.Add("ref_tablename", typeof(string));
            dt.Columns.Add("ref_columnname", typeof(string));

            return dt;
        }

        #endregion

        #region Table Builder

        /// <summary>
        /// Builds an array of <see cref="Table">Tables</see> using the reference tables as source.
        /// </summary>
        /// <param name="tablesTable">A DataTable created with <see cref="CreateTablesReferenceTable" />.</param>
        /// <param name="columnsTable">A DataTable created with <see cref="CreateColumnsReferenceTable" />.</param>
        /// <returns>An array of Tables.</returns>
        protected static Table[] BuildTables(DataTable tablesReference, DataTable columnsReference)
        {
            Converter<object, string> stringConverter = Convert.ToString;

            List<Table> tables = new List<Table>();

            foreach (DataRow row in tablesReference.Rows)
            {
                string name = (string) row["tablename"];
                string schema = GetRowValue<string>(row["tableschema"], null, stringConverter);
                string type = GetRowValue<string>(row["tabletype"], null, stringConverter);

                Table t = new Table();
                t.Schema = schema;
                t.Name = name;
                t.Alias = name;
                t.SourceType = (TableSourceType) Enum.Parse(typeof(TableSourceType), type, true);

                string columnFilter = GetFilter(t);
                DataRow[] columnRows = columnsReference.Select(columnFilter, "columnorder");

                BuildColumnsCollection(t, columnRows);

                tables.Add(t);
            }

            Table[] tableArray = tables.ToArray();

            return tableArray;
        }

        private static void BuildColumnsCollection(Table table, DataRow[] columnRows)
        {
            Converter<object, string> stringConverter = Convert.ToString;
            Converter<object, int?> intConverter = ConvertToNulableInt32;
            Converter<object, bool> boolConverter = Convert.ToBoolean;

            foreach (DataRow row in columnRows)
            {
                string name = (string) row["columnname"];

                Column c = new Column();

                c.Name         = name;
                c.SqlType      = GetRowValue<string>(row["type"], null, stringConverter);
                c.Length       = GetRowValue<int?>(row["length"], null, intConverter);
                c.OctetLength  = GetRowValue<int?>(row["octetlength"], null, intConverter);
                c.Precision    = GetRowValue<int?>(row["precision"], null, intConverter);
                c.Scale        = GetRowValue<int?>(row["scale"], null, intConverter);
                c.IsNullable   = GetRowValue<bool>(row["isnullable"], false, boolConverter);
                c.IsComputed   = GetRowValue<bool>(row["iscomputed"], false, boolConverter);
                c.IsPrimaryKey = GetRowValue<bool>(row["ispk"], false, boolConverter);
                c.IsForeignKey = GetRowValue<bool>(row["isfk"], false, boolConverter);

                c.Alias = name;

                // set the referenced objects
                if (c.IsForeignKey)
                {
                    string refSchema = GetRowValue<string>(row["ref_tableschema"], null, stringConverter);
                    string refTable  = GetRowValue<string>(row["ref_tablename"], null, stringConverter);
                    string refColumn = GetRowValue<string>(row["ref_columnname"], null, stringConverter);

                    if (refTable != null && refColumn != null)
                    {
                        c.ReferencedSchema = refSchema;
                        c.ReferencedTable = refTable;
                        c.ReferencedColumn = refColumn;
                    }
                }

                table.Columns.Add(c);
            }
        }

        private static string GetFilter(Table table)
        {
            string filter;

            if (table.Schema == null)
                filter = String.Format("tableschema is null and tablename = '{0}'", table.Name);
            else
                filter = String.Format("tableschema = '{0}' and tablename = '{1}'", table.Schema, table.Name);

            return filter;
        }

        private static T GetRowValue<T>(object o, T defaultValue, Converter<object, T> converter)
        {
            if (o == null || o == DBNull.Value)
            {
                return defaultValue;
            }
            else
            {
                return converter(o);
            }
        }

        private static int? ConvertToNulableInt32(object o)
        {
            try
            {
                return Convert.ToInt32(o);
            }
            catch
            {
                return null;
            }
        }

        #endregion
    }
}
